import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;
import java.util.Scanner;

import common.SQLCall;

/**
 * A tool to fill the Question_Comment table in the database. Comment are held
 * in a file.
 */
public class PopulateComment
{
	Connection conn;
	ArrayList<Integer> users;
	ArrayList<String> commentList = new ArrayList<String>();
	Random rnd = new Random();

	public static void main(String[] args)
	{
		PopulateComment pop = new PopulateComment();
		try
		{
			pop.connect();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}
		pop.setup();
		pop.generate();

		try
		{
			pop.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public PopulateComment()
	{

	}

	public PopulateComment(Connection c)
	{
		conn = c;
	}

	public void connect() throws SQLException
	{
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@146.187.134.17:1542:oracle10g", "cscd494",
				"R3tral435");

	}

	public void close() throws SQLException
	{
		conn.close();
	}

	public void setup()
	{
		File fileIn = new File("documents/comment.txt");
		try
		{
			Scanner fileScanner = new Scanner(fileIn);
			while (fileScanner.hasNext())
			{
				commentList.add(fileScanner.nextLine());
				// System.out.println(fileScanner.nextLine());
			}
		} catch (FileNotFoundException fnfe)
		{
			fnfe.printStackTrace();
		}

		users = SQLCall.getApeUsers(conn);

	}

	public void generate()
	{
		RandomDate rd = new RandomDate();
		ArrayList<Integer> questionList = new ArrayList<Integer>();
		int author = 0;
		int question_id = 0;
		try
		{
			PreparedStatement pstmt1 = conn
					.prepareStatement("SELECT question_id FROM QUESTION");
			ResultSet rst = pstmt1.executeQuery();

			while (rst.next())
			{
				questionList.add(rst.getInt("question_id"));
			}

			rst.close();
			pstmt1.close();
			PreparedStatement pstmt = conn
					.prepareStatement("INSERT INTO QUESTION_COMMENT (user_id, question_id, "
							+ "q_version, comment_date, comments) "
							+ "VALUES(?,?,0,?,?)");

			for (int i = 0; i < questionList.size(); i++)
			{

				author = users.get(rnd.nextInt(users.size()));
				question_id = questionList.get(i);

				pstmt.setInt(1, author);
				pstmt.setInt(2, question_id);
				pstmt.setDate(3, rd.next());
				pstmt.setString(4, commentList.get(rnd.nextInt(commentList
						.size())));
				pstmt.execute();
			}
			pstmt.close();

		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

	}
}
